package com.xdjmall.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import org.junit.jupiter.api.Test;

import com.xdjmall.dao.CategoryDao;
import com.xdjmall.entity.Category;
import com.xdjmall.util.DBUtils;
import com.xdjmall.util.QueryCondition;
import com.xdjmall.util.SQLHandler;

public class CategoryDaoImpl implements CategoryDao {

	@Override
	public Integer insert(Category t) throws SQLException {
		// TODO 苏予涵
		String sql = "insert into category(category_level,parent_id,category_name,category_rank,is_deleted,create_time,create_user,update_time,update_user) values(?,?,?,?,0,now(),?,now(),0)";
		try (Connection c = DBUtils.getConnection();
			PreparedStatement ps = c.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
			ps.setInt(1, t.getCategoryLevel());
			ps.setInt(2, t.getParentId());
			ps.setString(3, t.getCategoryName());
			ps.setInt(4, t.getCategoryRank());
			ps.setInt(5, t.getCreateUser());
			int row = ps.executeUpdate();
			ResultSet keySet = ps.getGeneratedKeys();
			if (keySet.next()) {
				t.setCategoryId(keySet.getInt(1));
			}
			return row;

		}
	}

	@Override
	public Integer delete(Integer id) throws SQLException {
		// 不实现
		return null;
	}

	@Override
	public Integer update(Category t) throws SQLException {
		// TODO 苏予涵
		String sql = "update category set update_time=now(),";
		if (t.getCategoryLevel() != null) {
			sql += " category_level=?,";
		}
		if (t.getParentId() != null) {
			sql += " parent_id=?,";
		}
		if (t.getCategoryName() != null) {
			sql += " category_name=?,";
		}
		if (t.getCategoryRank() != null) {
			sql += " category_rank=?,";
		}
		if (t.getCreateUser() != null) {
			sql += " create_user=?,";
		}

		// 截取并删除逗号 substring 包前不包后
		sql = sql.substring(0, sql.length() - 1);
		sql += " where category_id=?";

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			int num = 1;
			if (t.getCategoryLevel() != null) {
				ps.setInt(num++, t.getCategoryLevel());
			}
			if (t.getParentId() != null) {
				ps.setInt(num++, t.getParentId());
			}
			if (t.getCategoryName() != null) {
				ps.setString(num++, t.getCategoryName());
			}
			if (t.getCategoryRank() != null) {
				ps.setInt(num++, t.getCategoryRank());
			}
			if (t.getCreateUser() != null) {
				ps.setInt(num++, t.getCreateUser());
			}
			ps.setInt(num, t.getCategoryId());
			return ps.executeUpdate();
		}
	}

	@Override
	public Category selectById(Integer id) throws SQLException {
		// TODO 苏予涵
		String sql = "select * from category where category_id=?";
		Category u = null;
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			// 设置参数
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				u = new Category();
				u.setCategoryId(rs.getInt(1));
				u.setCategoryLevel(rs.getInt(2));
				u.setParentId(rs.getInt(3));
				u.setCategoryName(rs.getString(4));
				u.setCategoryRank(rs.getInt(5));
				u.setIsDeleted(rs.getBoolean(6));
				u.setCreateTime(rs.getDate(7).toLocalDate());
				u.setCreateUser(rs.getInt(8));
				u.setUpdateTime(rs.getDate(9).toLocalDate());
				u.setUpdateUser(rs.getInt(10));
				return u;
			} else {
				return null;
			}
		}
	}

	@Override
	public Integer selectCount() throws SQLException {
		// TODO 苏予涵
		String sql = " select count(*) from category where is_deleted=0";
		Connection c = DBUtils.getConnection();
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			return rs.getInt(1);
		} else {
			return null;
		}
	}

	@Override
	public List<Category> selectByPage(QueryCondition condition) throws SQLException {
		// 不实现
		return null;
	}

	@Override
	public Category selectByLevelAndName(Integer categoryLevel, String categoryName) throws SQLException {
		// TODO 孙庆洋
		String sql = "select category_id,category_level,parent_id,category_name,category_rank,is_deleted,create_user,update_user "
				+ "from category where category_level = ? and category_name = ?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, categoryLevel);
			ps.setString(2, categoryName);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Category category = new Category();
				category.setCategoryId(rs.getInt(1));
				category.setCategoryLevel(rs.getInt(2));
				category.setParentId(rs.getInt(3));
				category.setCategoryName(rs.getString(4));
				category.setCategoryRank(rs.getInt(5));
				category.setIsDeleted(rs.getBoolean(6));
				category.setCreateUser(rs.getInt(7));
				category.setUpdateUser(rs.getInt(8));
				return category;
			}
		}
		return null;
	}

	@Override
	public Integer selectTotalCategories(QueryCondition condition) throws SQLException {
		// 查数
		String sql = "select count(*) from category where is_deleted = 0 ";
		// 取等级和父类
		int categoryLevel = (int) condition.get("categoryLevel");
		int parentId = (int) condition.get("parentId");
		// 两个非0判断
		if (categoryLevel != 0) {
			sql += " and category_level = ?";
		}
		if (parentId != 0) {
			sql += " and parent_id = ?";
		}
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			if (categoryLevel != 0) {
				ps.setInt(1, categoryLevel);
				if (parentId != 0) {
					ps.setInt(2, parentId);
				}
			} else if (parentId != 0) {
				ps.setInt(1, parentId);
			}
			ResultSet rs = ps.executeQuery();
			rs.next();
			// 返回结果
			return rs.getInt(1);
		}

	}

	@Override
	public Integer deleteBatch(Integer[] ids) throws SQLException {
		// 假删改1
		String sql = "update category set update_time=now() and  is_deleted=1 where category_id " + SQLHandler.joinIn(ids);

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int row = ps.executeUpdate();
			return row;
		}

	}

	@Override
	public List<Category> selectByLevelAndParentIdsAndNumber(List<Integer> parentIds, Integer categoryLevel,
			Integer number) throws SQLException {
		List<Category> list = new ArrayList<>();
		// 查父类
		String sql = "SELECT * FROM category WHERE parent_id " + SQLHandler.joinIn(parentIds);
		sql += " and category_level = ? and is_deleted=0 order by category_rank desc limit ?";

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, categoryLevel);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			// get出来
			while (rs.next()) {
				Category ca = new Category();
				ca.setCategoryId(rs.getInt(1));
				ca.setCategoryLevel(rs.getInt(2));
				ca.setParentId(rs.getInt(3));
				ca.setCategoryName(rs.getString(4));
				ca.setCategoryRank(rs.getInt(5));
				ca.setIsDeleted(rs.getBoolean(6));
				ca.setCreateTime(rs.getDate(7).toLocalDate());
				ca.setCreateUser(rs.getInt(8));
				ca.setUpdateTime(rs.getDate(9).toLocalDate());
				ca.setUpdateUser(rs.getInt(10));
				list.add(ca);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}

	@Test
	public void categoryList() throws SQLException {
//		 QueryCondition qc = new QueryCondition();
//		 qc.setPage(1);
//		 qc.setPageSize(5);
//		 qc.put("parentId", 17);
//		 qc.put("categoryLevel", 3);
//		
//		 Integer row = selectTotalCategories(qc);
//		 System.out.println(row);
//		 List<Category> cs = selectCategoryList(qc);
//		 cs.forEach(System.out::println);
		List<Category> cs = selectByLevelAndParentIdsAndNumber(Arrays.asList(17, 18), 3, 8);
		cs.forEach(System.out::println);
	}

	@Override
	public List<Category> selectCategoryList(QueryCondition condition) throws SQLException {
		String sql = "select * from category where is_deleted=0 ";
		Integer categoryLevel = null;
		Integer parentId = null;
		List<Category> list = new ArrayList<>();

		if (condition != null) {
			categoryLevel = (Integer) condition.get("categoryLevel"); // 自己等级
			parentId = (Integer) condition.get("parentId"); // 父类别id
			if (categoryLevel != null) {
				sql += " and category_level =? ";
			}
			if (parentId != null) {
				sql += " and parent_id = ?";
			}

		}
		sql += " order by category_rank desc ";
		if (condition.getPage() != null && condition.getPageSize() != null) {
			sql += " limit ? ,?";
		}

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			int num = 1;
			if (categoryLevel != null) {
				ps.setInt(num++, categoryLevel);
			}
			if (parentId != null) {
				ps.setInt(num++, parentId);
			}
			Integer page = condition.getPage();
			Integer pageSize = condition.getPageSize();
			if (condition != null && page != null && pageSize != null) {
				ps.setInt(num++, (page - 1) * pageSize);
				ps.setInt(num, page * pageSize);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Category ca = new Category();
				ca.setCategoryId(rs.getInt(1));
				ca.setCategoryLevel(rs.getInt(2));
				ca.setParentId(rs.getInt(3));
				ca.setCategoryName(rs.getString(4));
				ca.setCategoryRank(rs.getInt(5));
				ca.setIsDeleted(rs.getBoolean(6));
				ca.setCreateTime(rs.getDate(7).toLocalDate());
				ca.setCreateUser(rs.getInt(8));
				ca.setUpdateTime(rs.getDate(9).toLocalDate());
				ca.setUpdateUser(rs.getInt(10));
				list.add(ca);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}
}
